Copyright Jana Schaich Borg/Attribution-NonCommercial 4.0 International (CC BY-NC 4.0)

MySQL Exercise: Subqueries and Derived Tables¶

Author/SQL analyst: Leon Hamnett¶

LinkedIn ¶

Introduction:¶

In this notebook, I have reproduced some of the SQL exercises I undertook as part of the course:

This course taught me how to write SQL queries from the very basics all the way upto complicated queries many lines long. The platforms used were MySQL via python and Teradata Database Management with SQL scratchpad.

The datasets used in the course were that of a startup company: Dognition which provided fun dog testing quizes for users to learn more about their dogs and a department store chain company based in the USA which sells a variety of goods: Dillards.

The Dognition data was in the range of 100,000s of rows and was relatively clean. The Dillards data was in the range of 10 million rows and was a subset of actual store data such as product information, store information and transaction data.

In this notebook, I will show some of the exercises I completed towards the end of the course whilst looking at when learning about subqueries and derived tables with regards to the Dognition dataset.

Exercises start below:¶

So why would you use subqueries?¶

Let's look at some examples and reasons a person might use subqueries.

Start by loading the sql library and database, and making the Dognition database your default database:

%load_ext sql
%sql mysql://studentuser:studentpw@localhost/dognitiondb
%sql USE dognitiondb

 * mysql://studentuser:***@localhost/dognitiondb
0 rows affected.

[]

1) "On the fly calculations" (or, doing calculations as you need them)¶

Question 1: How could you use a subquery to extract all the data from exam_answers that had test durations that were greater than the average duration for the "Yawn Warm-Up" game? Start by writing the query that gives you the average duration for the "Yawn Warm-Up" game by itself (and don't forget to exclude negative values; your average duration should be about 9934):

%%sql
select *
from exam_answers
where timestampdiff(minute,start_time,end_time) >
    (select avg(timestampdiff(minute,start_time,end_time))
    from exam_answers
    where test_name = "Yawn Warm-Up" and timestampdiff(minute,start_time,end_time) > 0);

 * mysql://studentuser:***@localhost/dognitiondb
11059 rows affected.

Question 2: Once you've verified that your subquery is written correctly on its own, incorporate it into a main query to extract all the data from exam_answers that had test durations that were greater than the average duration for the "Yawn Warm-Up" game (you will get 11059 rows):

%%sql
SELECT *
FROM exam_answers
WHERE TIMESTAMPDIFF(minute,start_time,end_time) >
(SELECT AVG(TIMESTAMPDIFF(minute,start_time,end_time)) AS AvgDuration
FROM exam_answers
WHERE TIMESTAMPDIFF(minute,start_time,end_time)>0 AND
test_name="Yawn Warm-Up");

This example shows you how subqueries allow you retrieve information dynamically, rather than having to hard code in specific numbers or names. This capability is particularly useful when you need to build the output of your queries into reports or dashboards that are supposed to display real-time information.

2) Testing membership¶

Subqueries can also be useful for assessing whether groups of rows are members of other groups of rows. To use them in this capacity, we need to know about and practice the IN, NOT IN, EXISTS, and NOT EXISTS operators.

Question 3: Use an IN operator to determine how many entries in the exam_answers tables are from the "Puzzles", "Numerosity", or "Bark Game" tests. You should get a count of 163022.

%%sql
select count(*)
from exam_answers
where test_name in ('Puzzles','Numerosity','Bark Game');

 * mysql://studentuser:***@localhost/dognitiondb
1 rows affected.

Question 4: Use a NOT IN operator to determine how many unique dogs in the dog table are NOT in the "Working", "Sporting", or "Herding" breeding groups. You should get an answer of 7961.

%%sql
select count(distinct dog_guid)
from dogs
where breed_group not in ("Working", "Sporting","Herding");

 * mysql://studentuser:***@localhost/dognitiondb
1 rows affected.

EXISTS and NOT EXISTS perform similar functions to IN and NOT IN, but EXISTS and NOT EXISTS can only be used in subqueries.

Question 5: How could you determine the number of unique users in the users table who were NOT in the dogs table using a NOT EXISTS clause? You should get the 2226, the same result as you got in Question 10 of MySQL Exercise 8: Joining Tables with Outer Joins.

%%sql
select count(distinct u.user_guid)
from users u
where not exists (select *
                 from dogs d
                 where u.user_guid = d.user_guid);

 * mysql://studentuser:***@localhost/dognitiondb
1 rows affected.

3) Accurate logical representations of desired output and Derived Tables¶

A third situation in which subqueries can be useful is when they simply represent the logic of what you want better than joins.

Subqueries and joins can often be used interchangeably. Some people strongly prefer one approach over another, but there is no consensus about which approach is best. When you are analyzing very large datasets, it's a good idea to test which approach will likely be faster or easier to troubleshoot for your particular application.

Question 6: Write a query using an IN clause and equijoin syntax that outputs the dog_guid, breed group, state of the owner, and zip or the owner for each distinct dog in the Working, Sporting, and Herding breed groups. (You should get 10,254 rows; the query will be a little slower than some of the others we have practiced)

%%sql
select distinct d.dog_guid,d.breed_group,u.state,u.zip
from dogs d,users u
where d.user_guid = u.user_guid
and d.breed_group in ('Working','Sporting','Herding');

 * mysql://studentuser:***@localhost/dognitiondb
10254 rows affected.

Question 7: Write the same query as in Question 6 using traditional join syntax.

%%sql
select distinct d.dog_guid,d.breed_group,u.state,u.zip
from dogs d join users u
on d.user_guid = u.user_guid
where d.breed_group in ('Working','Sporting','Herding');

 * mysql://studentuser:***@localhost/dognitiondb
10254 rows affected.

Question 8: Earlier we examined unique users in the users table who were NOT in the dogs table. Use a NOT EXISTS clause to examine all the users in the dogs table that are not in the users table (you should get 2 rows in your output).

%%sql
select d.user_guid,d.dog_guid
from dogs d
where not exists (select distinct user_guid
                 from users u
                 where u.user_guid = d.user_guid);

 * mysql://studentuser:***@localhost/dognitiondb
2 rows affected.

Question 9: We saw earlier that user_guid 'ce7b75bc-7144-11e5-ba71-058fbc01cf0b' still ends up with 1819 rows of output after a left outer join with the dogs table. If you investigate why, you'll find out that's because there are duplicate user_guids in the dogs table as well. How would you adapt the query we wrote earlier (copied below) to only join unique UserIDs from the users table with unique UserIDs from the dog table?

Join we wrote earlier:

SELECT DistinctUUsersID.user_guid AS uUserID, d.user_guid AS dUserID, count(*) AS numrows
FROM (SELECT DISTINCT u.user_guid 
      FROM users u) AS DistinctUUsersID 
LEFT JOIN dogs d
  ON DistinctUUsersID.user_guid=d.user_guid
GROUP BY DistinctUUsersID.user_guid
ORDER BY numrows DESC;

Let's build our way up to the correct query. To troubleshoot, let's only examine the rows related to user_guid 'ce7b75bc-7144-11e5-ba71-058fbc01cf0b', since that's the userID that is causing most of the trouble. Rewrite the query above to only LEFT JOIN distinct user(s) from the user table whose user_guid='ce7b75bc-7144-11e5-ba71-058fbc01cf0b'. The first two output columns should have matching user_guids, and the numrows column should have one row with a value of 1819:

%%sql
SELECT DistinctUUsersID.user_guid AS uUserID, d.user_guid AS dUserID, count(*) AS numrows
FROM (SELECT DISTINCT u.user_guid 
      FROM users u 
      where u.user_guid ='ce7b75bc-7144-11e5-ba71-058fbc01cf0b') AS DistinctUUsersID 
LEFT JOIN dogs d
  ON DistinctUUsersID.user_guid=d.user_guid
GROUP BY DistinctUUsersID.user_guid
ORDER BY numrows DESC;

 * mysql://studentuser:***@localhost/dognitiondb
1 rows affected.

Question 10: Now let's prepare and test the inner query for the right half of the join. Give the dogs table an alias, and write a query that would select the distinct user_guids from the dogs table (we will use this query as a inner subquery in subsequent questions, so you will need an alias to differentiate the user_guid column of the dogs table from the user_guid column of the users table).

select distinct user_guid as DistinctD
from dogs

Question 11: Now insert the query you wrote in Question 9 as a subquery on the right part of the join you wrote in question 8. The output should return columns that should have matching user_guids, and 1 row in the numrows column with a value of 1. If you are getting errors, make sure you have given an alias to the derived table you made to extract the distinct user_guids from the dogs table, and double-check that your aliases are referenced correctly in the SELECT and ON statements.

%%sql
SELECT DistinctUUsersID.user_guid AS uUserID, DistinctD.user_guid AS dUserID, count(*) AS numrows
FROM (SELECT DISTINCT u.user_guid 
      FROM users u 
      where u.user_guid ='ce7b75bc-7144-11e5-ba71-058fbc01cf0b') AS DistinctUUsersID 
LEFT JOIN (select distinct user_guid as DistinctD
            from dogs) as DistinctDUsersID
ON DistinctUUsersID.user_guid=DistinctDUsersID.user_guid
GROUP BY DistinctUUsersID.user_guid
ORDER BY numrows DESC;

%%sql
SELECT DistinctUUsersID.user_guid AS uUserID, DistictDUsersID.user_guid AS
dUserID, count(*) AS numrows
FROM (SELECT DISTINCT u.user_guid
        FROM users u
        WHERE u.user_guid='ce7b75bc-7144-11e5-ba71-058fbc01cf0b') AS
DistinctUUsersID
LEFT JOIN (SELECT DISTINCT d.user_guid
FROM dogs d) AS DistictDUsersID
ON DistinctUUsersID.user_guid=DistictDUsersID.user_guid
GROUP BY DistinctUUsersID.user_guid
ORDER BY numrows DESC

 * mysql://studentuser:***@localhost/dognitiondb
(MySQLdb._exceptions.OperationalError) (1054, "Unknown column 'DistinctD.user_guid' in 'field list'")
[SQL: SELECT DistinctUUsersID.user_guid AS uUserID, DistinctD.user_guid AS dUserID, count(*) AS numrows
FROM (SELECT DISTINCT u.user_guid 
      FROM users u 
      where u.user_guid ='ce7b75bc-7144-11e5-ba71-058fbc01cf0b') AS DistinctUUsersID 
LEFT JOIN (select distinct user_guid as DistinctD
            from dogs) as DistinctDUsersID
ON DistinctUUsersID.user_guid=DistinctDUsersID.user_guid
GROUP BY DistinctUUsersID.user_guid
ORDER BY numrows DESC;]
(Background on this error at: http://sqlalche.me/e/e3q8)

Question 12: Adapt the query from Question 10 so that, in theory, you would retrieve a full list of all the DogIDs a user in the users table owns, with its accompagnying breed information whenever possible. HOWEVER, BEFORE YOU RUN THE QUERY MAKE SURE TO LIMIT YOUR OUTPUT TO 100 ROWS WITHIN THE SUBQUERY TO THE LEFT OF YOUR JOIN. If you run the query without imposing limits it will take a very long time. If you try to limit the output by just putting a limit clause at the end of the outermost query, the database will still have to hold the entire derived tables in memory and join each row of the derived tables before limiting the output. If you put the limit clause in the subquery to the left of the join, the database will only have to join 100 rows of data.

%%sql
SELECT DistinctUUsersID.user_guid AS uUserID, DistictDUsersID.user_guid AS
dUserID,
DistictDUsersID.dog_guid AS DogID, DistictDUsersID.breed AS breed
FROM (SELECT DISTINCT u.user_guid
FROM users u
LIMIT 100) AS DistinctUUsersID
LEFT JOIN (SELECT DISTINCT d.user_guid, d.dog_guid, d.breed
FROM dogs d) AS DistictDUsersID
ON DistinctUUsersID.user_guid=DistictDUsersID.user_guid
GROUP BY DistinctUUsersID.user_guid;

Question 13: You might have a good guess by now about why there are duplicate rows in the dogs table and users table, even though most corporate databases are configured to prevent duplicate rows from ever being accepted. To be sure, though, let's adapt this query we wrote above:

SELECT DistinctUUsersID.user_guid AS uUserID, d.user_guid AS dUserID, count(*) AS numrows
FROM (SELECT DISTINCT u.user_guid FROM users u) AS DistinctUUsersID 
LEFT JOIN dogs d
  ON DistinctUUsersID.user_guid=d.user_guid
GROUP BY DistinctUUsersID.user_guid
ORDER BY numrows DESC

Add dog breed and dog weight to the columns that will be included in the final output of your query. In addition, use a HAVING clause to include only UserIDs who would have more than 10 rows in the output of the left join (your output should contain 5 rows).

%%sql
SELECT DistictUUsersID.user_guid AS userid, d.breed, d.weight, count(*) AS numrows
FROM (SELECT DISTINCT u.user_guid
FROM users u) AS DistictUUsersID
LEFT JOIN dogs d
ON DistictUUsersID.user_guid=d.user_guid
GROUP BY DistictUUsersID.user_guid
HAVING numrows>10
ORDER BY numrows DESC;

You can see that almost all of the UserIDs that are causing problems are Shih Tzus that weigh 190 pounds. As we learned in earlier lessons, Dognition used this combination of breed and weight to code for testing accounts. These UserIDs do not represent real data. These types of testing entries would likely be cleaned out of databases used in large established companies, but could certainly still be present in either new databases that are still being prepared and configured, or in small companies which have not had time or resources to perfect their data storage.

There are not very many incorrect entries in the Dognition database and most of the time these entries will not appreciably affect your queries or analyses. However, you have now seen the effects such entries can have in the rare cases when you need to implement outer joins on tables that have duplicate rows or linking columns with many to many relationships. Hopefully, understanding these rare cases has helped you understand more deeply the fundamental concepts behind joining tables in relational databases.

Exercises: Copyright Jana Schaich Borg/Attribution-NonCommercial 4.0 International (CC BY-NC 4.0)

user_guid	dog_guid
None	fd7c0a66-7144-11e5-ba71-058fbc01cf0b
None	fdbb6b7a-7144-11e5-ba71-058fbc01cf0b

script_detail_id	subcategory_name	test_name	step_type	start_time	end_time	dog_guid
537	Sociability	Sociability	question	2013-02-05 03:58:13	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
538	Emotions	Emotions	question	2013-02-05 03:58:31	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
539	Shy/Boldness	Shy/Boldness	question	2013-02-05 03:59:03	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
540	Perception	Perception	question	2013-02-05 03:59:10	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
541	Recall	Recall	question	2013-02-05 03:59:22	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
542	Attachment	Attachment	question	2013-02-05 03:59:36	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
543	Puzzles	Puzzles	question	2013-02-05 03:59:41	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
544	Shy/Boldness	Shy/Boldness	question	2013-02-05 04:00:00	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
545	Shy/Boldness	Shy/Boldness	question	2013-02-05 04:00:16	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
546	Partnership	Partnership	question	2013-02-05 04:00:35	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
547	Emotions	Emotions	question	2013-02-05 04:00:46	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
548	Perception	Perception	question	2013-02-05 04:00:54	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
549	Obedience	Obedience	question	2013-02-05 04:01:01	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
550	Attachment	Attachment	question	2013-02-05 04:01:15	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
551	Attachment	Attachment	question	2013-02-05 04:01:40	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
552	Puzzles	Puzzles	question	2013-02-05 04:02:02	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
553	Recall	Recall	question	2013-02-05 04:02:30	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
554	Obedience	Obedience	question	2013-02-05 04:03:00	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
555	Perception	Perception	question	2013-02-05 04:03:29	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
556	Sociability	Sociability	question	2013-02-05 04:03:37	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
557	Sociability	Sociability	question	2013-02-05 04:03:48	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
558	Sociability	Sociability	question	2013-02-05 04:03:56	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
559	Puzzles	Puzzles	question	2013-02-05 04:04:07	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
560	Excitability	Excitability	question	2013-02-05 04:05:31	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
561	Attachment	Attachment	question	2013-02-05 04:05:47	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
562	Partnership	Partnership	question	2013-02-05 04:05:57	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
563	Recall	Recall	question	2013-02-05 04:06:06	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
564	Emotions	Emotions	question	2013-02-05 04:06:30	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
565	Obedience	Obedience	question	2013-02-05 04:07:38	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
566	Emotions	Emotions	question	2013-02-05 04:08:58	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
567	Attachment	Attachment	question	2013-02-05 04:09:05	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
568	Sociability	Sociability	question	2013-02-05 04:09:40	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
569	Sociability	Sociability	question	2013-02-05 04:09:44	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
570	Partnership	Partnership	question	2013-02-05 04:10:03	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
571	Shy/Boldness	Shy/Boldness	question	2013-02-05 04:10:11	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
572	Perception	Perception	question	2013-02-05 04:10:16	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
573	Shy/Boldness	Shy/Boldness	question	2013-02-05 04:10:26	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
574	Shy/Boldness	Shy/Boldness	question	2013-02-05 04:11:12	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
575	Shy/Boldness	Shy/Boldness	question	2013-02-05 04:11:21	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b
576	Shy/Boldness	Shy/Boldness	question	2013-02-05 04:11:34	2013-10-02 20:18:06	fd27b272-7144-11e5-ba71-058fbc01cf0b

dog_guid	breed_group	state	zip
fd27b272-7144-11e5-ba71-058fbc01cf0b	Sporting	ND	58201
fd27b5ba-7144-11e5-ba71-058fbc01cf0b	Herding	MA	1005
fd27b6b4-7144-11e5-ba71-058fbc01cf0b	Sporting	CT	6820
fd27b79a-7144-11e5-ba71-058fbc01cf0b	Sporting	IL	60093
fd27b948-7144-11e5-ba71-058fbc01cf0b	Working	WA	98001
fd27baec-7144-11e5-ba71-058fbc01cf0b	Working	None	None
fd27c0fa-7144-11e5-ba71-058fbc01cf0b	Sporting	WA	98117
fd27c1c2-7144-11e5-ba71-058fbc01cf0b	Sporting	WA	98117
fd27c6cc-7144-11e5-ba71-058fbc01cf0b	Herding	None	None
fd27c7d0-7144-11e5-ba71-058fbc01cf0b	Sporting	CA	95003
fd27c8d4-7144-11e5-ba71-058fbc01cf0b	Working	VA	22903
fd27c9e2-7144-11e5-ba71-058fbc01cf0b	Herding	None	None
fd27cc80-7144-11e5-ba71-058fbc01cf0b	Working	None	None
fd27cf28-7144-11e5-ba71-058fbc01cf0b	Sporting	WY	82401
fd27cfaa-7144-11e5-ba71-058fbc01cf0b	Herding	IL	60030
fd27d02c-7144-11e5-ba71-058fbc01cf0b	Herding	VA	22044
fd27d2ca-7144-11e5-ba71-058fbc01cf0b	Sporting	CA	90045
fd27d34c-7144-11e5-ba71-058fbc01cf0b	Herding	A	68128
fd27d3d8-7144-11e5-ba71-058fbc01cf0b	Herding	A	68128
fd27d45a-7144-11e5-ba71-058fbc01cf0b	Sporting	AZ	85635
fd27d6e4-7144-11e5-ba71-058fbc01cf0b	Sporting	None	None
fd27d770-7144-11e5-ba71-058fbc01cf0b	Herding	NC	27713
fd27db08-7144-11e5-ba71-058fbc01cf0b	Herding	CO	80304
fd27de8c-7144-11e5-ba71-058fbc01cf0b	Herding	None	None
fd27df18-7144-11e5-ba71-058fbc01cf0b	Herding	None	None
fd27e1e8-7144-11e5-ba71-058fbc01cf0b	Herding	83	6230
fd27e31e-7144-11e5-ba71-058fbc01cf0b	Herding	83	6230
fd27e580-7144-11e5-ba71-058fbc01cf0b	Sporting	CA	94952
fd27ec1a-7144-11e5-ba71-058fbc01cf0b	Sporting	NC	27371
fd27ee72-7144-11e5-ba71-058fbc01cf0b	Herding	None	None
fd27efb2-7144-11e5-ba71-058fbc01cf0b	Sporting	CA	92116
fd27f110-7144-11e5-ba71-058fbc01cf0b	Working	WA	98225
fd27f4c6-7144-11e5-ba71-058fbc01cf0b	Herding	1	249081
fd27f732-7144-11e5-ba71-058fbc01cf0b	Herding	1	249081
fd2801b4-7144-11e5-ba71-058fbc01cf0b	Working	None	None
fd280236-7144-11e5-ba71-058fbc01cf0b	Working	OH	45220
fd2802c2-7144-11e5-ba71-058fbc01cf0b	Sporting	None	None
fd280344-7144-11e5-ba71-058fbc01cf0b	Working	FL	32934
fd2803d0-7144-11e5-ba71-058fbc01cf0b	Sporting	None	None
fd280452-7144-11e5-ba71-058fbc01cf0b	Herding	None	None

dog_guid	breed_group	state	zip
fd27b272-7144-11e5-ba71-058fbc01cf0b	Sporting	ND	58201
fd27b5ba-7144-11e5-ba71-058fbc01cf0b	Herding	MA	1005
fd27b6b4-7144-11e5-ba71-058fbc01cf0b	Sporting	CT	6820
fd27b79a-7144-11e5-ba71-058fbc01cf0b	Sporting	IL	60093
fd27b948-7144-11e5-ba71-058fbc01cf0b	Working	WA	98001
fd27baec-7144-11e5-ba71-058fbc01cf0b	Working	None	None
fd27c0fa-7144-11e5-ba71-058fbc01cf0b	Sporting	WA	98117
fd27c1c2-7144-11e5-ba71-058fbc01cf0b	Sporting	WA	98117
fd27c6cc-7144-11e5-ba71-058fbc01cf0b	Herding	None	None
fd27c7d0-7144-11e5-ba71-058fbc01cf0b	Sporting	CA	95003
fd27c8d4-7144-11e5-ba71-058fbc01cf0b	Working	VA	22903
fd27c9e2-7144-11e5-ba71-058fbc01cf0b	Herding	None	None
fd27cc80-7144-11e5-ba71-058fbc01cf0b	Working	None	None
fd27cf28-7144-11e5-ba71-058fbc01cf0b	Sporting	WY	82401
fd27cfaa-7144-11e5-ba71-058fbc01cf0b	Herding	IL	60030
fd27d02c-7144-11e5-ba71-058fbc01cf0b	Herding	VA	22044
fd27d2ca-7144-11e5-ba71-058fbc01cf0b	Sporting	CA	90045
fd27d34c-7144-11e5-ba71-058fbc01cf0b	Herding	A	68128
fd27d3d8-7144-11e5-ba71-058fbc01cf0b	Herding	A	68128
fd27d45a-7144-11e5-ba71-058fbc01cf0b	Sporting	AZ	85635
fd27d6e4-7144-11e5-ba71-058fbc01cf0b	Sporting	None	None
fd27d770-7144-11e5-ba71-058fbc01cf0b	Herding	NC	27713
fd27db08-7144-11e5-ba71-058fbc01cf0b	Herding	CO	80304
fd27de8c-7144-11e5-ba71-058fbc01cf0b	Herding	None	None
fd27df18-7144-11e5-ba71-058fbc01cf0b	Herding	None	None
fd27e1e8-7144-11e5-ba71-058fbc01cf0b	Herding	83	6230
fd27e31e-7144-11e5-ba71-058fbc01cf0b	Herding	83	6230
fd27e580-7144-11e5-ba71-058fbc01cf0b	Sporting	CA	94952
fd27ec1a-7144-11e5-ba71-058fbc01cf0b	Sporting	NC	27371
fd27ee72-7144-11e5-ba71-058fbc01cf0b	Herding	None	None
fd27efb2-7144-11e5-ba71-058fbc01cf0b	Sporting	CA	92116
fd27f110-7144-11e5-ba71-058fbc01cf0b	Working	WA	98225
fd27f4c6-7144-11e5-ba71-058fbc01cf0b	Herding	1	249081
fd27f732-7144-11e5-ba71-058fbc01cf0b	Herding	1	249081
fd2801b4-7144-11e5-ba71-058fbc01cf0b	Working	None	None
fd280236-7144-11e5-ba71-058fbc01cf0b	Working	OH	45220
fd2802c2-7144-11e5-ba71-058fbc01cf0b	Sporting	None	None
fd280344-7144-11e5-ba71-058fbc01cf0b	Working	FL	32934
fd2803d0-7144-11e5-ba71-058fbc01cf0b	Sporting	None	None
fd280452-7144-11e5-ba71-058fbc01cf0b	Herding	None	None